<h1 >关于索引</h1>

<ul>
<li><p >大多Sql引擎都有能力支持多至16个列，256字节的索引，一条mysql查询在查询时，只能选择一个索引使用</p></li>
<li><p >要选择性的使用索引。不要在值的可能性非常少的列上使用索引，例如性别列的值只有男和女。</p></li>
<li><p >在Unique列上定义Unique index</p></li>
<li><p >在需要进行排序或分组(即进行group by或order by操作)的列上建立索引。如果待排序的列有多个，在这些列上建立多列索引(compound index)</p></li>
<li><p >避免建立使用不到的索引，这会浪费空间并影响写入速度</p></li>
<li><p >避免重复的索引，例如已经有索引(col1,col2),那么就不要建立(col1)索引</p></li>
<li><p >如果字段的前几个字符的索引已经可以找出数据，那么使用key(col_name(8))在数据的前面几个字符建立索引。</p></li>
<li><p >在经常进行连接，但是没有指定为外键的列上建立索引</p></li>
<li><p >MyISAM和InnoDB存储引擎还支持对BLOB和TEXT列的索引。当索引一个BLOB或TEXT列时，你必须为索引指定前缀长度。</p></li>
<li><p >Optimize table可以压缩和排序index，注意不要频繁运行(MyISAM表)</p></li>
<li><p >Analyze table可以更新索引数据</p></li>
</ul>

<h1 >创建索引</h1>

<pre class='lang'><code>    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
        [USING index_type]
        ON tbl_name (col_name [(length)] [ASC | DESC],...)
</code></pre>

<p >length为字符数，可选用于varchar和char列，必选用于blob和text列，对于MyISAM和InnoDB表；最长为1000字节(333个UTF8汉字字符)</p>

<p >index_type：myisam和innodb只能用btree,所以无需指定。MEMORY/HEAP则支持hash和Btree，默认为hash</p>

<p >FULLTEXT索引只能对CHAR, VARCHAR和TEXT列编制索引，并且只能在MyISAM表中编制</p>

<h1 >删除索引</h1>

<pre class='lang'><code>DROP INDEX index_name ON tbl_name
</code></pre>

<h1 >查看索引：</h1>

<pre class='lang'><code class=''>mysql&gt; CREATE INDEX idx_username ON users(username);
        mysql&gt; SHOW INDEX FROM users;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          1 | idx_username |            1 | username    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
</code></pre>

<h1 >优化查询性能</h1>

<blockquote>
  <p >在分析查询性能时，考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面，用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。</p>
</blockquote>

<p >下面的一个简单例子可以说明这一过程，其中country.code列有索引，其他列没有索引：</p>

<pre class='lang'><code class=''>EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | country | const | PRIMARY       | PRIMARY | 3       | const |  1   | Using index |
|  1 | SIMPLE      | city    | ALL   | NULL          | NULL    | NULL    | NULL  | 4079 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
</code></pre>

<p >从上面看到这条查询由两条简单查询完成，第一条查询只有一行，即常量查询(type类型为const)，第二条对city表的查询没有索引，因此扫描了city表中的所有的行
优化以后再次执行：</p>

<pre class='lang'><code class=''>CREATE INDEX idx_ccode ON city(countrycode);
EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | country | const | PRIMARY       | PRIMARY   | 3       | const |    1 | Using index |
|  1 | SIMPLE      | city    | ref   | idx_ccode     | idx_ccode | 3       | const |  333 | Using where |
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+
2 rows in set (0.01 sec)
</code></pre>

<p >在这个例子中，MySQL现在只需要扫描city表中的333条记录就可产生一个结果集，其扫描记录数几乎减少了90％！自然，数据库资源的查询速度更快，效率更高。</p>

<p >还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以 STRAIGHT_JOIN 而不只是SELECT开头。</p>

<h1 >EXPLAIN语法</h1>

<blockquote>
  <p >EXPLAIN [EXTENDED] SELECT select_options</p>
</blockquote>

<p >EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。</p>

<p >MySQL用一次扫描多次联接（single-sweep multi-join）的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。</p>

<p >当使用EXTENDED关键字时，EXPLAIN在执行结束后将产生附加信息，用SHOW WARNINGS可以查看到这些信息。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。</p>

<h5 >EXPLAIN的每个输出行提供一个表的相关信息，并且每个行包括下面的列：</h5>

<ul>
<li><p >id</p>

<p >SELECT识别符。这是SELECT的查询序列号。</p></li>
<li><p >select_type</p>

<p >SELECT类型，可以为以下任何一种：</p>

<ul>
<li>SIMPLE   简单SELECT(不使用UNION或子查询)</li>
<li>PRIMARY  嵌套查询的最外面的SELECT</li>
<li>UNION    UNION子句的SELECT语句</li>
<li>DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query</li>
<li>UNION RESULT   Result of a UNION.</li>
<li>SUBQUERY     First SELECT in subquery</li>
<li>DEPENDENT SUBQUERY  First SELECT in subquery, dependent on outer query</li>
<li>DERIVED   Derived table SELECT (subquery in FROM clause)</li>
<li>UNCACHEABLE SUBQUERY  A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query</li>
<li>UNCACHEABLE UNION  The second or later select in a UNION that belongs t- an uncacheable subquery (see UNCACHEABLE SUBQUERY)</li>
</ul></li>
<li><p >table</p>

<p >输出的行所引用的表。</p></li>
<li><p >type</p>

<p >联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：</p>

<ul>
<li><p >system  表仅有一行(=系统表)。这是const联接类型的一个特例。</p></li>
<li><p >const   表最多有一个匹配行，它将在查询开始时被读取出来。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！</p>

<p >const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。在下面的查询中，tbl_name可以用于const表：</p>

<pre class='lang'><code>SELECT * from tbl_name WHERE primary_key=1;

SELECT * from tbl_name  WHERE primary_key_part1=1 AND primary_key_part2=2;
</code></pre></li>
<li><p >eq_ref  对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。</p>

<p >eq_ref可以用于使用 = 操作符比较的带unique,primary key索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。</p>

<p >在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：</p>

<pre class='lang'><code>SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column  AND ref_table.key_column_part2=1;
</code></pre></li>
<li><p >ref   对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则        使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。</p>

<p >ref可以用于使用=或&lt;=>操作符的带index索引的列。</p>

<p >在下面的例子中，MySQL可以使用ref联接来处理ref_tables：</p>

<pre class='lang'><code>SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table   WHERE ref_table.key_column=other_table.column; 

SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column  AND ref_table.key_column_part2=1;
</code></pre></li>
<li><p >ref_or_null  该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。</p>

<p >在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：</p>

<pre class='lang'><code>SELECT * FROM ref_table  WHERE key_column=expr OR key_column IS NULL;
</code></pre></li>
<li><p >index_merge  该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。详细信息参见7.2.6节，“索引合并优化”。</p></li>
<li><p >unique_subquery  该类型替换了下面形式的IN子查询的ref：</p>

<pre class='lang'><code>value IN (SELECT primary_key FROM single_table WHERE some_expr)
</code></pre>

<p >unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。</p></li>
<li><p >index_subquery该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：</p>

<pre class='lang'><code>value IN (SELECT key_column  FROM single_table WHERE some_expr)
</code></pre></li>
<li><p >range 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。</p>

<p >当使用=、&lt;>、>、>=、&lt;、&lt;=、IS NULL、&lt;=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range：</p>

<pre class='lang'><code>SELECT * FROM tbl_name  WHERE key_column = 10;

SELECT * FROM tbl_name  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name  WHERE key_part1= 10 AND key_part2 IN (10,20,30);
</code></pre></li>
<li><p >index  该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。</p>

<p >当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。</p></li>
<li><p >ALL 对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。</p></li>
</ul></li>
<li><p >possible_keys</p>

<p >possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。</p>

<p >如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。</p></li>
<li><p >key</p>

<p >key列显示MySQL实际决定使用的索引。如果没有选择索引，键是NULL，意味着你需要优化一下。</p>

<p >如果要使用possible_keys中的其他索引，你可以在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。参见13.2.7节，“SELECT语法”。</p>

<p >对于MyISAM和BDB表，运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表，可以使用myisamchk --analyze。参见13.5.2.1节，“ANALYZE TABLE语法”和5.9.4节，“表维护和崩溃恢复”。</p></li>
<li><p >key_len</p>

<p >key_len列显示MySQL决定使用的索引长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。</p></li>
<li><p >ref</p>

<p >ref列显示使用哪个列或常数与key一起从表中选择行。</p></li>
<li><p >rows</p>

<p >rows列显示MySQL认为它执行查询时必须检查的行数。</p></li>
<li><p >Extra</p>

<p >该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：</p>

<ul>
<li><p >Distinct  MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。</p></li>
<li><p >Not exists  MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。</p>

<p >下面是一个可以这样优化的查询类型的例子：</p>

<pre class='lang'><code>SELECT * from t1 LEFT JOIN t2 ON t1.id=t2.id  WHERE t2.id IS NULL；
</code></pre>

<p >假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。</p></li>
<li><p >range checked for each record (index map: #)MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。关于适用性标准的描述参见7.2.5节，“范围优化”和7.2.6节，“索引合并优化”，不同的是前面表的所有列值已知并且认为是常量。</p>

<p >这并不很快，但比执行没有索引的联接要快得多。</p></li>
<li><p >Using filesort MySQL需要使用临时表来完成排序排序我了比临时是，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。参见7.2.12节，“MySQL如何优化ORDER BY”。</p></li>
<li><p >Using index 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。</p></li>
<li><p >Using temporary 为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。</p></li>
<li><p >Using where WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。</p>

<p >如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。</p></li>
<li><p >Using sort_union(...), Using union(...), Using intersect(...)  表明mysql在执行优化时使用了哪种索引合并算法</p></li>
<li><p >Using index for group-by</p>

<p >类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。</p></li>
</ul></li>
</ul>

<h1 >加速SELECT查询</h1>

<blockquote>
  <p >总的来说，要想使一个较慢速 SELECT ... WHERE 更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。</p>
</blockquote>

<h5 >下面是一些加速对MyISAM表的查询的一般建议：</h5>

<ul>
<li><p >为了帮助MySQL更好地优化查询，在一个装载数据后的表上运行ANALYZE TABLE或myisamchk --analyze。这样为每一个索引更新指出有相同值的行的平均行数的值（当然，如果只有一个索引，这总是1。）MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk --description --verbose可以显示索引分布信息。</p></li>
<li><p >要想根据一个索引排序一个索引和数据，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！</p></li>
</ul>

<h2 >MySQL优化器怎样优化WHERE子句</h2>

<blockquote>
  <p >该节讨论为处理WHERE子句而进行的优化。例子中使用了SELECT语句，但相同的优化也适用DELETE和UPDATE语句中的WHERE子句。</p>
</blockquote>

<ul>
<li><p >去除不必要的括号：<code>(a AND b) AND c OR (((a AND b) AND (c AND d))))</code>  优化为：<code>(a AND b AND c) OR (a AND b AND c AND d)</code></p></li>
<li><p >常量重叠：  <code>(a&lt;b AND b=c) AND a=5 优化为： b&gt;5 AND b=c AND a=5</code></p></li>
<li><p >去除常量条件(由于常量重叠需要)：<code>(B&gt;=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)</code>优化为：<code>B=5 OR B=6</code></p></li>
<li><p >对于MyISAM和HEAP表，在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对NOT NULL表达式也这样做。</p></li>
<li><p >无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。</p></li>
<li><p >如果不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。</p></li>
<li><p >对于联接内的每个表，构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。</p></li>
<li><p >所有常数的表在查询中比其它表先读出。常数表为：</p>

<ul>
<li><p >空表或只有1行的表。</p></li>
<li><p >与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。</p></li>
<li><p >下列的所有表用作常数表：</p></li>
<li><p >mysql> SELECT * FROM t WHERE primary_key=1;</p></li>
<li><p >mysql> SELECT * FROM t1,t2  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;</p></li>
</ul></li>
<li><p >尝试所有可能性便可以找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。</p></li>
<li><p >如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。</p></li>
<li><p >如果使用SQL_SMALL_RESULT，MySQL使用内存中的一个临时表。</p></li>
<li><p >每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。</p></li>
<li><p >在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。</p></li>
<li><p >输出每个记录前，跳过不匹配HAVING子句的行。</p></li>
<li><p >下列查询使用索引按排序顺序检索行，不用另外的排序：</p>

<ul>
<li><p >SELECT ... FROM tbl_name  ORDER BY key_part1,key_part2,... ;</p></li>
<li><p >SELECT ... FROM tbl_name  ORDER BY key_part1 DESC, key_part2 DESC, ... ;</p></li>
</ul></li>
<li><p >下列查询仅使用索引树就可以解决(假设索引的列为数值型)：</p>

<ul>
<li><p >SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;</p></li>
<li><p >SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2;</p></li>
<li><p >SELECT key_part2 FROM tbl_name GROUP BY key_part1;</p></li>
</ul></li>
</ul>

<h2 >MySQL优化器扫描范围优化</h2>

<p >range访问方法使用单一索引来搜索包含在一个或几个索引值距离内的表记录的子集。可以用于单部分或多元素索引。后面的章节将详细描述如何从WHERE子句提取区间。</p>

<h3 >单元素索引的范围访问方法</h3>

<h5 >单元素索引范围条件的定义如下：</h5>

<ul>
<li><p >对于BTREE和HASH索引，当使用<code>=、&lt;=&gt;、IN、IS NULL或者IS NOT NULL</code>操作符时，关键元素与常量值的比较关系对应一个范围条件。</p></li>
<li><p >对于BTREE索引，当使用<code>&gt;、&lt;、&gt;=、&lt;=、BETWEEN、!=或者&lt;&gt;，或者LIKE 'pattern'(其中 'pattern'不以通配符开始)</code>操作符时，关键元素与常量值的比较关系对应一个范围条件。</p></li>
<li><p >对于所有类型的索引，多个范围条件结合OR或AND则产生一个范围条件。</p></li>
</ul>

<h5 >前面描述的“常量值”系指：</h5>

<ul>
<li><p >查询字符串中的常量</p></li>
<li><p >同一联接中的const或system表中的列</p></li>
<li><p >无关联子查询的结果</p></li>
<li><p >完全从前面类型的子表达式组成的表达式</p></li>
</ul>

<h5 >下面是一些WHERE子句中有范围条件的查询的例子：</h5>

<pre class='lang'><code>SELECT * FROM t1   WHERE key_col &gt; 1   AND key_col &lt; 10;
SELECT * FROM t1   WHERE key_col = 1   OR key_col IN (15,18,20);
SELECT * FROM t1   WHERE key_col LIKE 'ab%'   OR  key_col BETWEEN 'bar' AND 'foo';
</code></pre>

<p >请注意在常量传播阶段部分非常量值可以转换为常数。</p>

<p >MySQL尝试为每个可能的索引从WHERE子句计算范围条件：不能用于构成范围条件的条件被放弃，产生重叠范围的条件组合到一起，并且产生空范围的条件被删除。</p>

<h5 >例如：考虑下面的语句，其中key1是有索引的列，nonkey没有索引：</h5>

<pre class='lang'><code>SELECT * FROM t1 WHERE
(key1 &lt; 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 &lt; 'bar' AND nonkey = 4) OR
(key1 &lt; 'uux' AND key1 &gt; 'z');
</code></pre>

<h6 >key1的提取过程如下：</h6>

<ol>
<li><p >从原始WHERE子句开始：</p></li>
<li><p ><code>(key1 &lt; 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR</code></p></li>
<li><p ><code>(key1 &lt; 'bar' AND nonkey = 4) OR</code></p></li>
<li><p ><code>(key1 &lt; 'uux' AND key1 &gt; 'z')</code></p></li>
<li><p >删除nonkey = 4和key1 LIKE '%b'，因为它们不能用于key1的范围扫描。删除它们的正确途径是用TRUE替换它们，以便进行范围扫描时不会丢失匹配的记录。用TRUE替换它们后，可以得到：</p>

<p ><code>(key1 &lt; 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR</code></p>

<p ><code>(key1 &lt; 'bar' AND TRUE) OR</code></p>

<p ><code>(key1 &lt; 'uux' AND key1 &gt; 'z')</code></p></li>
<li><p >取消总是为true或false的条件：</p>

<ul>
<li><p ><code>(key1 LIKE 'abcde%' OR TRUE)</code>总是true</p></li>
<li><p ><code>(key1 &lt; 'uux' AND key1 &gt; 'z')</code>总是false</p></li>
</ul>

<p >用常量替换这些条件，我们得到：</p>

<p ><code>(key1 &lt; 'abc' AND TRUE) OR (key1 &lt; 'bar' AND TRUE) OR (FALSE)</code></p></li>
<li><p >删除不必要的TRUE和FALSE常量，我们得到</p>

<p ><code>(key1 &lt; 'abc') OR (key1 &lt; 'bar')</code></p></li>
<li><p >将重叠区间组合成一个产生用于key1范围扫描的最终条件：</p>

<p ><code>(key1 &lt; 'bar')</code></p></li>
</ol>

<p >范围条件提取算法可以处理嵌套的任意深度的AND/OR结构，并且其输出不依赖条件在WHERE子句中出现的顺序。</p>

<h3 >多元素索引的范围访问方法</h3>

<p >多元素索引的范围条件是单元素索引的范围条件的扩展。多元素索引的范围条件将索引记录限制到一个或几个关键元组内。使用索引的顺序，通过一系列关键元组来定义关键元组区间。</p>

<h2 >索引合并优化</h2>

<blockquote>
  <p >索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。</p>
  
  <p >在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素。</p>
</blockquote>

<h3 >索引合并算法</h3>

<ul>
<li>交集</li>
<li>联合</li>
<li>排序并集</li>
</ul>

<h5 >注释：索引合并优化算法具有以下几个已知缺陷：</h5>

<ul>
<li><p >如果可以对某些关键字进行范围扫描，则不考虑索引合并。例如，下面的查询：</p>

<p >SELECT * FROM t1 WHERE (goodkey1 &lt; 10 OR goodkey2 &lt; 20) AND badkey &lt; 30;</p>

<p >对于该查询，可以有两个方案：</p>

<pre class='lang'><code>1. 使用`(goodkey1 &lt; 10 OR goodkey2 &lt; 20)`条件进行索引合并扫描。
2. 使用`badkey &lt; 30`条件进行范围扫描。
</code></pre>

<p >然而，优化器只考虑第2个方案。如果这不是你想要的，你可以通过使用IGNORE INDEX或FORCE INDEX让优化器考虑index_merge。下面的查询使用索引合并执行：</p>

<p >SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)
WHERE (goodkey1 &lt; 10 OR goodkey2 &lt; 20) AND badkey &lt; 30;</p>

<p >SELECT * FROM t1 IGNORE INDEX(badkey)
WHERE (goodkey1 &lt; 10 OR goodkey2 &lt; 20) AND badkey &lt; 30;</p></li>
<li><p >如果查询有一个复杂的WHERE子句，有较深的AND/OR嵌套关系，MySQL不选择该优选方案，通过下面的识别法则尝试分布各条件：</p>

<ul>
<li><p ><code>(x AND y) OR z = (x OR z) AND (y OR z)</code></p></li>
<li><p ><code>(x OR y) AND z = (x AND z) OR (y AND z)</code></p></li>
</ul></li>
</ul>

<h3 >交集访问算法</h3>

<h5 >该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件，每个条件为下面之一：</h5>

<ol>
<li>以这种形式，即索引有确切的N部分(即包括了所有索引部分)： key_part1=const1 AND key_part2=const2 ... AND key_partN=constN</li>
<li>任何InnoDB或BDB表的主键的范围条件。</li>
</ol>

<h5 >下面是一些例子：</h5>

<pre class='lang'><code>SELECT * FROM innodb_table WHERE primary_key &lt; 10 AND key_col1=20;
SELECT * FROM tbl_name  WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;
</code></pre>

<p >索引合并交集算法同时对所有使用的索引进行扫描，并产生从合并的索引扫描接收的行序列的交集。</p>

<p >如果使用的索引包括查询中使用的所有列，则不会扫描表的记录，在这种情况下EXPLAIN的输出Extra字段含有“Using index”。下面是一个此类查询的例子：</p>

<pre class='lang'><code>SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;
</code></pre>

<p >如果使用到的索引没有覆盖查询的所有列，那么只有当根据所有的索引字段的范围条件找到行以后才对这些满足条件的行进行扫描，而不会去扫描全表。</p>

<p >如果某个合并条件是InnoDB或BDB表的主键的一个条件，那么这个条件不会用于记录查询，而是用于对其他范围条件的返回结果进行过滤选择。</p>

<h3 >并集访问算法</h3>

<h5 >该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件的时候，每个条件为下面之一：</h5>

<ol>
<li>以这种形式，即索引有确切的N部分(即包括了所有索引部分)：  key_part1=const1 OR key_part2=const2 ... OR key_partN=constN</li>
<li>任何InnoDB或BDB表的主键的范围条件。</li>
<li>索引合并方法交集算法适用的范围条件。</li>
</ol>

<p >下面是一些例子：</p>

<pre class='lang'><code>SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;
SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR  (key3='foo' AND key4='bar') AND key5=5;
</code></pre>

<h3 >排序并集访问算法</h3>

<p >当WHERE子句被mysql转换为由OR连接的一些范围条件，但这个where条件却不适用索引合并并集算法的时候，可以使用这个算法。</p>

<h5 >下面是一些例子：</h5>

<pre class='lang'><code>SELECT * FROM tbl_name  WHERE  key_col1 &lt; 10 OR key_col2 &lt; 20;
SELECT * FROM tbl_name  WHERE (key_col1 &gt; 10 OR key_col2 = 20) AND nonkey_col=30;
</code></pre>

<p >排序联合算法和联合算法的区别是排序联合算法必须先索取所有记录的行ID，然后在返回记录前对它们进行排序。</p>

<h2 >MySQL如何优化ORDER BY</h2>

<p >如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试下面的策略：</p>

<ul>
<li>增加sort_buffer_size变量的大小。</li>
<li>增加read_rnd_buffer_size变量的大小。</li>
<li>更改tmpdir指向具有大量空闲空间的专用文件系统。该选项接受几个使用round-robin(循环)模式的路径。</li>
</ul>

<h3 >可以使用索引的 Order By</h3>

<p >下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分：</p>

<pre class='lang'><code>SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; 
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part2;   //where子句的常量提供了order by中缺失的索引前缀
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
</code></pre>

<h3 >不能使用索引的 Order By</h3>

<pre class='lang'><code>SELECT * FROM t1 ORDER BY key1, key2; /* 单列索引*/
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2; /*缺失索引前缀列*/
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;  /*排序方式不同*/
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;  /* where子句使用了key2索引，所以order by无法使用key1索引，因为mysql一次只能使用一个索引 */
</code></pre>

<ul>
<li><p >有很多表一起做连接，而且读取的记录中在 ORDER BY 中的字段都不全是来自第一个非常数的表中（也就是说，在 EXPLAIN 分析的结果中的第一个表的连接类型不是 const）。</p></li>
<li><p >order by的前面有group by子句，但这两个子句中出现的列不一致，所以group by使用了一个索引后，order by不能使用这个索引。</p></li>
</ul>

<h2 >优化GROUP BY</h2>

<p >mysql可以对符合松散索引和紧凑索引扫描算法的语句使用优化，从而使用索引来排序，避免创建临时表</p>

<h3 >松散索引扫描</h3>

<p >此类查询的EXPLAIN输出显示Extra列的Using indexforgroup-by</p>

<h5 >符合以下条件的情形，mysql可以使用索引排序：</h5>

<ul>
<li>查询没有跨表</li>
<li>索引中的列，必须是完整索引而不能是前缀索引。c1 VARCHAR(20), INDEX (c1(10)), 这个索引无法使用loose index scan.</li>
<li>group by中的列必须全部是索引中的列，第一列必须是索引中的第一列，其他列的顺序与索引中的顺序一致且连续</li>
<li>只支持min()和max()两种聚合函数，且其中的列必须是索引中的列，而且是跟在group by的所有列之后的那个位置上的列。</li>
<li>索引中没有被group by用到的列，在where条件中使用，值等于常量</li>
</ul>

<h5 >假设在表t1(c1,c2,c3,c4)上有索引idx(c1,c2,c3)，索引松散扫描可以在下面的查询中生效：</h5>

<pre class='lang'><code>SELECT c1, c2 FROM t1 GROUP BY c1, c2;  /* select和group by的列相同，且都可使用到索引 */
SELECT DISTINCT c1, c2 FROM t1;         /* 等价于上面的 */
SELECT c1, MIN(c2) FROM t1 GROUP BY c1; /* c2列是索引中的列，c2是c1后面的列,select中c2列使用min()或max() */
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 &gt; const GROUP BY c1, c2; /* c3是索引中的列，c3是c2后面的列，使用了min()和max() */
SELECT c2 FROM t1 WHERE c1 &lt; const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c1 &lt; const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
</code></pre>

<h5 >下面的查询无法使用索引松散排序(5.1版)：</h5>

<pre class='lang'><code>SELECT c1, c2 FROM t1 GROUP BY c1, c2, c4;    /* c4不是索引中的列 */
SELECT c1, c2 ,c3 FROM t1 GROUP BY c2, c3;   /* 不是从左到右的索引列顺序*/
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;      /* 5.1不支持MIN() or MAX()以外的聚合函数*/
SELECT c1, c3 FROM t1 GROUP BY c1, c2;       /* 如果增加WHERE c3 = const, 松散索引扫描就可以生效了 */
</code></pre>

<h5 >5.5增加了对AVG(DISTINCT), SUM(DISTINCT), COUNT(DISTINCT) 的支持. 但有一些限制要求：</h5>

<ul>
<li>AVG(DISTINCT) and SUM(DISTINCT) 只支持一个列。 COUNT(DISTINCT) 可使用多个索引列(可不考虑顺序但必须连续)。</li>
<li>查询中没有使用Distinct关键字，没有使用group by子句</li>
</ul>

<h5 >假设an index idx(c1,c2,c3) on table t1(c1,c2,c3,c4)，下面的查询在5.5中可使用索引松散扫描</h5>

<pre class='lang'><code>SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
</code></pre>

<p >但下面的不可以：</p>

<pre class='lang'><code>SELECT DISTINCT COUNT(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;
</code></pre>

<h3 >紧凑索引扫描</h3>

<p >如果group by中的列不完整，但wehere条件中存在该列的常量条件，mysql可以提取这个常量来实现完整索引。</p>

<p >下面的查询可以使用紧凑索引扫描排序(假定我们已经提及了表t1的索引idx)：</p>

<pre class='lang'><code>GROUP BY缺少c2列，但已经由条件c2 = 'a'覆盖。
    SELECT c1，c2，c3 FROM t1 WHERE c2 = 'a' GROUP BY c1，c3;
GROUP BY不以关键字的第1个元素开始，但是有一个条件提供该元素的常量：
    SELECT c1，c2，c3 FROM t1 WHERE c1 = 'a' GROUP BY c2，c3;
</code></pre>

<h2 >Optimizing Subqueries with EXISTS Strategy</h2>

<p >mysql优化器可以对 outer_expr IN (select  inner_expr.....) 进行优化 (IN等价于 = ANY)，在编写语句和定义列时按以下原则可以使这个优化更高效：</p>

<ul>
<li>加速outer_expr IN (select  inner_expr.....)的最有效方法就是将引用的列声明为not null类型。</li>
<li>如果outer_expr有null值，转换为下面的形式：(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))</li>
</ul>
